package com.monster.dao;

import com.monster.pojo.Album;
import com.monster.pojo.Playlist;
import com.monster.pojo.Singer;
import com.monster.pojo.Song;
import com.monster.pojo.User;

import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;

/**
 * Song的数据访问层实现类，包含查询song表和相关联的其他表得到与song相关的数据的方法
 * @author cyb
 */
public class SongDaoImpl extends BaseDao implements SongDao {
    private SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");

    /**
     * 通过id查询song的全部信息
     * @param id
     * @return Song 存储song信息的对象
     */
    @Override
    public Song getSongById(Long id) {
        Song song = new Song();
        String sql = "SELECT * FROM song WHERE song_id=?";
        Object obj[] = {id};
        rs = this.executeQuery(sql, obj);
        try {
            while (rs.next()) {
                song.setSongId(rs.getLong(1));
                song.setSongName(rs.getString(2));
                song.setLanguage(rs.getString(3));
                song.setGenre(rs.getString(4));
                song.setPublicTime(sdf.format(rs.getDate(5)));
                song.setLyric(rs.getString(6));
                song.setTime(rs.getString(7));
                song.setPoster(rs.getString(8));
                song.getAlbum().setAlbumId(rs.getLong(9));
                song.setUrl(rs.getString(11));
                song.setProfile(rs.getString(12));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return song;
    }

    /**
     * 通过id查询song表得到song的id、name、time
     * @param id
     * @return Song对象
     */
    @Override
    public Song getSimpleSongById(Long id) {
        Song song = new Song();
        // 查询song表获得song的id、name、时长
        String sql = "SELECT song_name,song_time FROM song WHERE song_id=?";
        Object obj[] = {id};
        rs = this.executeQuery(sql, obj);
        try {
            while (rs.next()) {
                song.setSongId(id);
                song.setSongName(rs.getString(1));
                song.setTime(rs.getString(2));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return song;
    }

    /**
     * 通过song_id联表查询歌手信息
     * @param songId
     * @return Singer集合
     */
    @Override
    public ArrayList<Singer> listSingersBySongId(Long songId) {
        ArrayList<Singer> singers = new ArrayList<>();
        String sql = "SELECT si.singer_id,si.singer_name FROM singer si " +
                "LEFT JOIN song_singer ss ON si.singer_id=ss.singer_id " +
                "LEFT JOIN song so ON so.song_id=ss.song_id WHERE so.song_id=?";
        Object[] obj = {songId};
        rs = this.executeQuery(sql, obj);
        try {
            while (rs.next()) {
                Singer singer = new Singer();
                singer.setSingerId(rs.getLong(1));
                singer.setSingerName(rs.getString(2));
                singers.add(singer);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return singers;
    }

    /**
     * 通过song_id联表查询专辑信息
     * @param songId
     * @return Album 专辑信息
     */
    @Override
    public Album getAlbumBySongId(Long songId) {
        Album album = null;
        String sql = "SELECT a.album_id,album_name FROM album a,song WHERE song_id=?";
        Object[] obj = {songId};
        rs = this.executeQuery(sql, obj);
        try {
            while (rs.next()) {
                album = new Album();
                album.setAlbumId(rs.getLong(1));
                album.setAlbumName(rs.getString(2));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return album;
    }

    /**
     * 通过song_id联表查询3条歌单信息，并按播放量降序
     * @param songId
     * @return Playlist集合，歌单信息
     */
    @Override
    public ArrayList<Playlist> listPlaylistsBySongId(Long songId) {
        ArrayList<Playlist> playlists = new ArrayList<>(3);
        String sql = "SELECT p.playlist_id,playlist_name,playlist_poster,user_id FROM playlist p " +
                "LEFT JOIN song_playlist sp ON sp.playlist_id=p.playlist_id " +
                "LEFT JOIN song s ON s.song_id=sp.song_id " +
                "WHERE s.song_id=? ORDER BY playlist_playcount DESC LIMIT 3";
        Object[] obj = {songId};
        rs = this.executeQuery(sql, obj);
        try {
            while (rs.next()) {
                Playlist p = new Playlist();
                p.setPlaylistId(rs.getLong(1));
                p.setPlaylistName(rs.getString(2));
                p.setPlaylistPoster(rs.getString(3));
                User user = new User();
                user.setId(rs.getLong(4));
                p.setUser(user);
                playlists.add(p);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return playlists;
    }

    /**
     * 根据关键字模糊查询歌曲信息，按热度降序
     * @param key
     * @return Song集合，歌曲信息
     */
    @Override
    public ArrayList<Song> listSongsByFuzzy(String key) {
        ArrayList<Song> songs = new ArrayList<>();
        String sql = "SELECT * FROM song WHERE song_name LIKE '%" + key + "%' ORDER BY song_hot DESC";
        Object[] params = null;
        rs = this.executeQuery(sql, params);
        try {
            while (rs.next()) {
                Song song = new Song();
                song.setSongId(rs.getLong(1));
                song.setSongName(rs.getString(2));
                song.setTime(rs.getString(7));
                song.setPoster(rs.getString("song.song_poster"));
                //song.setSingers(listSingersBySongId(song.getSongId()));
                //song.getAlbum().setAlbumId(rs.getLong(9));
                songs.add(song);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return songs;
    }


}
